﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using SqlSugar;

namespace TestProject
{
    public class DbContexts
    {
        /// <summary>
        /// 数据库连接字符串(私有字段)
        /// </summary>
        private static string DbConnString = ConfigurationManager.ConnectionStrings["DbConnString"].ConnectionString;

        /// <summary>
        /// 数据库类型(私有字段)
        /// </summary>
        private static string _databaseType = ConfigurationManager.ConnectionStrings["DbConnString"].ProviderName;

        /// <summary>
        /// 用来处理事务多表查询和复杂的操作
        /// 注意：不能写成静态的
        /// </summary>
        public SqlSugarClient Db;

        /// <summary>
        /// 数据库上下文
        /// </summary>
        /// <returns></returns>
        public DbContexts()
        {
            SqlSugar.DbType dbType;
            switch (_databaseType)
            {
                case "System.Data.SqlClient":
                    dbType = SqlSugar.DbType.SqlServer;
                    break;
                case "System.Data.SqliteClient":
                    dbType = SqlSugar.DbType.Sqlite;
                    break;
                case "MySql.Data.MySqlClient":
                    dbType = SqlSugar.DbType.MySql;
                    break;
                case "Oracle.ManagedDataAccess.Client":
                    dbType = SqlSugar.DbType.Oracle;
                    break;
                default:
                    dbType = SqlSugar.DbType.SqlServer;
                    break;
            }

            Db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = DbConnString,//数据库连接串 
                DbType = dbType,//数据库类型
                InitKeyType = InitKeyType.SystemTable,//从数据库中读取主键和自增列信息         //InitKeyType.Attribute,//从特性读取主键和自增列信息
                IsAutoCloseConnection = true,//开启自动释放模式和EF原理一样
            });
            //SQL执行前事件
            Db.Aop.OnLogExecuting = (sql, pars) =>
            {
                //var listArray = pars.ToDictionary(key=>key.ParameterName,value=>value.Value);

                ////循环输出SQL参数信息
                //for (int i = 0; i < pars.Length; i++)
                //{
                //    ("[参数名称]" + pars[i].ParameterName + "\r\n" + "[参数值]:" + pars[i].Value);
                //}

                //打印SQL语句 写入日志中
                //LogHelper.WriteLog("[SQL语句]:" + sql + "\r\n");
            };
        }

        #region 库表操作
        /// <summary>
        /// 备份表
        /// </summary>
        /// <param name="oldname">旧表名称</param>
        /// <param name="newname">新表名称</param>
        /// <returns></returns>
        public bool BackupTable(string oldname, string newname)
        {
            if (!Db.DbMaintenance.IsAnyTable(newname, false))
            {
                return Db.DbMaintenance.BackupTable(oldname, newname, 0);
            }
            return false;
        }
        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public bool DropTable(string tablename)
        {
            return Db.DbMaintenance.DropTable(tablename);
        }

        /// <summary>
        /// 清空表
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public bool TruncateTable(string tablename)
        {
            return Db.DbMaintenance.TruncateTable(tablename);
        }

        #region CodeFirst 类-->表
        /// <summary>
        /// 创建单一表 
        /// </summary>
        /// <param name="entityType"></param>
        public void CreateTable(Type entityType)
        {
            Db.CodeFirst.SetStringDefaultLength(200).BackupTable().InitTables(entityType);
        }

        /// <summary>
        /// 批量创建表
        /// </summary>
        /// <param name="entityTypes"></param>
        public void CreateTables(Type[] entityTypes)
        {
            Db.CodeFirst.SetStringDefaultLength(200).BackupTable().InitTables(entityTypes);
        }
        #endregion

        #region DbFirst 表-->类
        /// <summary>
        /// 根据数据库表 生成实体类文件
        /// 数据库表名统一格式:XX_XXX  如:Sys_UserInfo
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="nameSpace"></param>
        /// <param name="tableName"></param>
        public void CreateClassFiles(string filePath, string nameSpace, string tableName)
        {
            #region 格式化 实体类文件名称
            //循环遍历 数据库里的所有表  
            foreach (var item in Db.DbMaintenance.GetTableInfoList())
            {
                string entityName = string.Empty;
                if (item.Name.Contains("_"))
                {
                    var tbName = item.Name.Split('_');
                    entityName = tbName[1] + "Entity";
                    Db.MappingTables.Add(entityName, item.Name);
                }
                else
                {
                    entityName = item.Name + "Entity";
                    Db.MappingTables.Add(entityName, item.Name);
                }
                //循环遍历  当前表的所有列
                foreach (var col in Db.DbMaintenance.GetColumnInfosByTableName(item.Name))
                {
                    //所有列全部转大写
                    Db.MappingColumns.Add(col.DbColumnName.ToUpper(), col.DbColumnName, entityName);
                }
            }
            #endregion

            //生成指定表名的实体类文件
            if (!string.IsNullOrEmpty(tableName))
            {
                //生成带有SqlSugar特性的实体类文件
                Db.DbFirst.Where(tableName).IsCreateAttribute(true).CreateClassFile(filePath, nameSpace);
            }
            //生成所有表的实体类文件
            else
            {
                //生成带有SqlSugar特性的实体类文件
                Db.DbFirst.IsCreateAttribute(true).CreateClassFile(filePath, nameSpace);
            }
        }
        #endregion
        #endregion

        #region 事务操作
        /// <summary>
        /// 开启事务
        /// </summary>
        public void BeginTran()
        {
            Db.Ado.BeginTran();
        }

        /// <summary>
        /// 提交事务 
        /// </summary>
        public void CommitTran()
        {
            Db.Ado.CommitTran();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTran()
        {
            Db.Ado.RollbackTran();
        }
        #endregion

        #region 原生Sql
        /// <summary>
        /// 针对于 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual int ExecuteCommand(string sql, params SugarParameter[] pars)
        {
            return Db.Ado.ExecuteCommand(sql, pars);
        }

        /// <summary>
        /// 返回集合
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回DataTable</returns>
        public virtual DataTable GetDataTable(string sql, params SugarParameter[] pars)
        {
            return Db.Ado.GetDataTable(sql, pars);
        }

        /// <summary>
        /// 返回多个集合
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public virtual DataSet GetDataSet(string sql, params SugarParameter[] pars)
        {
            return Db.Ado.GetDataSetAll(sql, pars);
        }

        /// <summary>
        /// 调用存储过程 返回Output参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns>返回int</returns>
        public virtual int ExecProcToInt(string sql, params SugarParameter[] pars)
        {
            int proc_count = 0;
            try
            {
                BeginTran();
                proc_count = Db.Ado.UseStoredProcedure().GetInt(sql, pars);
                CommitTran();
            }
            catch (Exception ex)
            {
                RollbackTran();
                throw ex;
            }

            return proc_count;
        }

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns>返回DataTable</returns>
        public virtual DataTable ExecProcToDT(string sql, params SugarParameter[] pars)
        {
            DataTable dt = new DataTable();
            try
            {
                BeginTran();
                dt = Db.Ado.UseStoredProcedure().GetDataTable(sql, pars);
                CommitTran();
            }
            catch (Exception ex)
            {
                RollbackTran();
                throw ex;
            }

            return dt;
        }

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns>返回DataSet</returns>
        public virtual DataSet ExecProcToDS(string sql, params SugarParameter[] pars)
        {
            DataSet ds = new DataSet();
            try
            {
                BeginTran();
                ds = Db.Ado.UseStoredProcedure().GetDataSetAll(sql, pars);
                CommitTran();
            }
            catch (Exception ex)
            {
                RollbackTran();
                throw ex;
            }

            return ds;
        }
        #endregion

        #region 泛型CURD
        /// <summary>
        /// 校验数据是否存在
        /// </summary>
        /// <param name="expression">Lambda表达式（查询条件）</param>
        /// <returns></returns>
        public bool Any<TEntity>(Expression<Func<TEntity, bool>> expression) where TEntity : class, new()
        {
            return Db.Queryable<TEntity>().Any(expression);
        }

        /// <summary>
        /// 检查信息总条数
        /// </summary>
        /// <param name="expression">Lambda表达式（查询条件）</param>
        /// <returns></returns>
        public int Count<TEntity>(Expression<Func<TEntity, bool>> expression) where TEntity : class, new()
        {
            return Db.Queryable<TEntity>().Count(expression);
        }

        /// <summary>
        /// 查询实体
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns>单条记录</returns>
        public TEntity FindEntity<TEntity>(object keyValue) where TEntity : class, new()
        {
            return Db.Queryable<TEntity>().InSingle(keyValue);
        }

        /// <summary>
        /// 查询实体集合
        /// </summary>
        /// <returns></returns>
        public ISugarQueryable<TEntity> Queryable<TEntity>() where TEntity : class, new()
        {
            return Db.Queryable<TEntity>();
        }

        /// <summary>
        /// 自定义条件查询
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public ISugarQueryable<TEntity> Queryable<TEntity>(Expression<Func<TEntity, bool>> expression) where TEntity : class, new()
        {
            return Db.Queryable<TEntity>().Where(expression);
        }

        /// <summary>
        /// 通过SQL语句查询
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public ISugarQueryable<TEntity> Queryable<TEntity>(string strSql) where TEntity : class, new()
        {
            return Db.SqlQueryable<TEntity>(strSql);
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Insertable<TEntity>(TEntity entity) where TEntity : class, new()
        {
            return Db.Insertable(entity).ExecuteCommand();
        }

        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="entities"></param>
        /// <returns></returns>
        public int Insertable<TEntity>(List<TEntity> entities) where TEntity : class, new()
        {
            return Db.Insertable(entities).ExecuteCommand();
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Updateable<TEntity>(TEntity entity) where TEntity : class, new()
        {
            return Db.Updateable(entity).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
        }

        /// <summary>
        /// 自定义条件编辑
        /// </summary>
        /// <param name="predicate"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public int Updateable<TEntity>(Expression<Func<TEntity, TEntity>> content, Expression<Func<TEntity, bool>> predicate) where TEntity : class, new()
        {
            return Db.Updateable(content).Where(predicate).ExecuteCommand();
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Deleteable<TEntity>(TEntity entity) where TEntity : class, new()
        {
            return Db.Deleteable(entity).ExecuteCommand();
        }

        /// <summary>
        /// 自定义条件删除
        /// </summary>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public int Deleteable<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class, new()
        {
            return Db.Deleteable(predicate).ExecuteCommand();
        }
        #endregion
    }
}
